package DAO;

import Util.ConnectionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Categorias;

public class CategoriasDAO {

    private Connection con;

    public CategoriasDAO() throws Exception {
        con = ConnectionUtil.getConn();
    }

    public void insert(Categorias item) throws SQLException {
        String SQL = "INSERT INTO CATEGORIAS VALUES (?,?,?)";
        PreparedStatement stmt = con.prepareStatement(SQL);

        stmt.setInt(1, item.getCodigo());
        stmt.setString(2, item.getNome());
        stmt.setBoolean(3, item.isStatus());
        stmt.execute();
    }

    public void delete(int codigo) throws SQLException {
        String SQL = "DELETE FROM CATEGORIAS WHERE CTG_ID = ?";
        PreparedStatement stmt = con.prepareStatement(SQL);
        stmt.setInt(1, codigo);
        stmt.execute();
    }

    public void update(Categorias item) throws SQLException {
        String SQL = "UPDATE CATEGORIAS SET CTG_NOME = ?, CTG_STATUS = ? WHERE CTG_ID = ?";
        PreparedStatement stmt = con.prepareStatement(SQL);
        stmt.setString(1, item.getNome());
        stmt.setBoolean(2, item.isStatus());
        stmt.setInt(3, item.getCodigo());
        stmt.execute();
    }

    public Categorias findByCodigo(int codigo) throws Exception {
        String SQL = "SELECT CTG_ID, CTG_NOME, CTG_STATUS FROM CATEGORIAS WHERE CTG_ID = ?";
        PreparedStatement p = con.prepareStatement(SQL);
        p.setInt(1, codigo);

        ResultSet rs = p.executeQuery();

        if (rs.next()) {
            Categorias cat = new Categorias();
            cat.setCodigo(rs.getInt("CTG_ID"));
            cat.setNome(rs.getString("CTG_NOME"));
            cat.setStatus(rs.getBoolean("CTG_STATUS"));
            return cat;

        } else {
            return null;
        }
    }

    public List<Categorias> findByNome(String nome) throws Exception {
        String SQL = "SELECT CTG_ID, CTG_NOME, CTG_STATUS FROM CATEGORIAS WHERE CTG_NOME LIKE ?";
        PreparedStatement p = con.prepareStatement(SQL);
        p.setString(1, "%"+nome+"%");

        ResultSet rs = p.executeQuery();
        
        List<Categorias> lsCategorias = new ArrayList<>();
        while (rs.next()) {
            Categorias cat = new Categorias();
            cat.setCodigo(rs.getInt("CTG_ID"));
            cat.setNome(rs.getString("CTG_NOME"));
            cat.setStatus(rs.getBoolean("CTG_STATUS"));
            lsCategorias.add(cat);
        }
        return lsCategorias;
    }

    public List<Categorias> findAll() throws SQLException {

        String SQL = "SELECT CTG_ID, CTG_NOME, CTG_STATUS FROM CATEGORIAS WHERE 1=1";

        PreparedStatement p = con.prepareStatement(SQL);
        ResultSet rs = p.executeQuery();

        List<Categorias> lsCategorias = new ArrayList<>();
        while (rs.next()) {
            Categorias cat = new Categorias();
            cat.setCodigo(rs.getInt("CTG_ID"));
            cat.setNome(rs.getString("CTG_NOME"));
            cat.setStatus(rs.getBoolean("CTG_STATUS"));
            lsCategorias.add(cat);
        }
        return lsCategorias;
    }
}